package com.xrh.core.generater.utils;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import com.xrh.core.bean.OrderByInfo;
import com.xrh.core.bean.Queryer;
import com.xrh.core.bean.SQLQueryerParser;
import com.xrh.core.generater.bean.DataBaseConfig;
import com.xrh.core.utils.PagingUtils;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.convert.Convert;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONUtil;
import lombok.extern.slf4j.Slf4j;

/**
 * @function sqlserver数据库工具类
 * @author ronghui.xiao
 * @date 2023年05月30日
 */
@Slf4j
public class SqlServerDataBaseUtils {
	
	public static void main(String[] args) {
		
		try {
			
			DataBaseConfig dbBaseConfig = DataBaseConfig.createSqlServerConfig("127.0.0.1",  "1433", "test"  ,  "sa", "root");
			
//			String sql = "select * from person";
//			
//			List<LinkedHashMap<String , Object>> map = executeQuerySQL(dbBaseConfig, sql);
//			
//			System.out.println(map);
			
//			System.out.println(getDbNameList(dbBaseConfig));
//			System.out.println(getCreateTableDDL(dbBaseConfig , "person"));
//			System.out.println(getCreateTableDDL(dbBaseConfig , "person_view"));
			
			Queryer queryer = new Queryer();
			
//			queryer.eq("id", "10");
			
//			queryer.ge("id", "10");
			
			queryer.orderBy(OrderByInfo.asc("person_name") , OrderByInfo.desc("id"));
			
			List<LinkedHashMap<String , Object>> list = findRecordListByQueryer(dbBaseConfig , "person" , queryer , 1 , 5);
			
			System.out.println(JSONUtil.toJsonPrettyStr(list));
			
			System.out.println(findRecordListCountByQueryer(dbBaseConfig, "person", queryer));
//			
//			String sql = "update person set person_name='张飞_修改' where id = '1'";
			
			//更新
//			executeUpdateSQL(dbBaseConfig, sql);
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
	
	/**
	 * @function 获取数据库名称列表
	 * @param dataBaseConfig
	 * @author 肖荣辉
	 * @date 2023年5月30
	*/
	public static  List<String> getDbNameList(DataBaseConfig dataBaseConfig) throws Exception{
		
		//1. JDBC连接MYSQL的代码很标准。 
		Connection conn = getConnection(dataBaseConfig);

		//2. 下面就是获取表的信息。 
		DatabaseMetaData  databaseMetaData = conn.getMetaData(); 
		
		ResultSet resultSet = databaseMetaData.getCatalogs();
		
		List<String> dbNameList = new ArrayList<String>();
		
		while (resultSet.next()) {
			dbNameList.add(StrUtil.trim(resultSet.getString("TABLE_CAT")));
		}
		
		conn.close();
		
		return dbNameList;
	}
	
	/**
	 * @function 获取创建表的DDL语句
	 * @author 肖荣辉
	 * @date 2023年5月30
	*/
	public static String getCreateTableDDL(DataBaseConfig dataBaseConfig , String tableName) throws Exception {
		
		String oldTableName = tableName;
		
		tableName = tableName.indexOf(".") != -1 ? tableName.substring(tableName.indexOf(".") + 1) : tableName;
		
		String sql = "SELECT  tableName = CASE WHEN a.colorder = 1 THEN d.name ELSE '' END ,\n" + 
								"        comment = ISNULL(g.[value], '') ,\n" + 
								"        columnName = a.name ,\n" + 
								"        type = CASE WHEN b.name IN ( 'varchar', 'nvarchar' )\n" + 
								"                  THEN b.name + '('\n" + 
								"                       + CAST(COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS VARCHAR(4))\n" + 
								"                       + ')'\n" + 
								"                  WHEN b.name = 'decimal'\n" + 
								"                  THEN b.name + '('\n" + 
								"                       + CAST(COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS VARCHAR(4))\n" + 
								"                       + ','\n" + 
								"                       + CAST(COLUMNPROPERTY(a.id, a.name, 'Scale') AS VARCHAR(4))\n" + 
								"                       + ')'\n" + 
								"                  ELSE b.name\n" + 
								"             END\n" + 
								"FROM syscolumns a \n" + 
								"    LEFT JOIN systypes b ON a.xusertype = b.xusertype  \n" + 
								"    INNER JOIN sysobjects d ON a.id = d.id AND d.xtype = 'U' AND d.name <> 'dtproperties'    \n" + 
								"    LEFT JOIN sys.extended_properties g ON a.id = g.major_id AND a.colid = g.minor_id    \n" + 
								"WHERE d.name = '"+tableName+"'    \n" + 
								"ORDER BY a.id , a.colorder";
		
		
		//1. JDBC连接MYSQL的代码很标准。 
		Connection conn = getConnection(dataBaseConfig);
		
		Statement statement = conn.createStatement();
	    
	    ResultSet rs = statement.executeQuery(sql);
		
	    String ddl = oldTableName + " {\n";
	    
		while (rs.next()) {
			
			String columnName = rs.getString("columnName");
			String type = rs.getString("type");
			String comment = rs.getString("comment");
			
			ddl += "\t " + columnName + " " ;
			ddl += "\t " + type+ " " ;
			ddl += "\t " + comment + " ,  \n";
		}
		ddl += "}";
		conn.close();
		
		return ddl;
		
	}
	
	
	/**
	 * @function 获取数据库所有表的名称集合
	 * @author ronghui.xiao
	 * @date 2017年6月24日
	 */
	public static List<String> getTableNames(DataBaseConfig dataBaseConfig) throws Exception{
		
		List<String> list = new ArrayList<String>();
		
		//1. JDBC连接MYSQL的代码很标准。 
		Connection conn = getConnection(dataBaseConfig);

		//2. 下面就是获取表的信息。 
		DatabaseMetaData  databaseMetaData = conn.getMetaData(); 
		
		///*其中"%"就是表示*的意思，也就是任意所有的意思。其中m_TableName就是要获取的数据表的名字，如果想获取所有的表的名字，就可以使用"%"来作为参数了。*/
		ResultSet tableRet = databaseMetaData.getTables(conn.getCatalog(), null  , null , new String[]{"TABLE","VIEW"}); 
		
		while(tableRet.next()){
			
//			String TABLE_CAT = tableRet.getString("TABLE_CAT");
			String TABLE_SCHEM = tableRet.getString("TABLE_SCHEM");
			String TABLE_NAME = tableRet.getString("TABLE_NAME");
//			String TABLE_TYPE = tableRet.getString("TABLE_TYPE");
//			String REMARKS = tableRet.getString("REMARKS");
			
			//系统表不要
			if("INFORMATION_SCHEMA".equalsIgnoreCase(TABLE_SCHEM) || "sys".equalsIgnoreCase(TABLE_SCHEM) )
				continue;
			
			list.add("dbo".equalsIgnoreCase(TABLE_SCHEM) ? TABLE_NAME :  TABLE_SCHEM + "." + TABLE_NAME );
			
		}
		
		conn.close();
		
		return list;
	}
	
	/**
     * @function 创建查询SQL
     * @author 肖荣辉
     * @date 2023年1月4日
     */
    public static String createQuerySql(Queryer queryer) {
    	
    	if(queryer == null)
    		return "";
    	
    	String querySql = queryer.parseQueryer(new SQLQueryerParser());
    	
    	querySql = StrUtil.isBlank(querySql) ? "" : " where " + querySql;
    	
    	return querySql;
    }
    
	/**
	 * @function 分页查询某个表的记录总条数
	 * @author 肖荣辉
	 * @date 2023年5月30
	*/
	public static long findRecordListCountByQueryer(DataBaseConfig dataBaseConfig , String tableName , Queryer  queryer)  throws Exception {
		
        long count = 0;

        //获取表名
        Connection conn = getConnection(dataBaseConfig);

        Statement statement = conn.createStatement();

        String sql = "select count(*) COUNT from " + tableName + createQuerySql(queryer);

        log.info("执行统计SQl : {}" , sql);
        
        ResultSet  rs = statement.executeQuery(sql);

        while (rs != null && rs.next()) {
            count = rs.getLong("COUNT");
            break;
        }
        
        conn.close();
            
        return count;
	}
	
	/**
	 * @function 分页查询某个表的其中一条记录
	 * @author 肖荣辉
	 * @date 2023年5月30
	*/
	public static <T> T findOneRecordByQueryer(Class<T> clazz , DataBaseConfig dataBaseConfig , String tableName , Queryer  queryer)  throws Exception {
		
		List<LinkedHashMap<String , Object>> list = findRecordListByQueryer(dataBaseConfig , tableName , queryer , 1 , 1);
		
		return CollUtil.isEmpty(list) ? null : BeanUtil.copyProperties(list.get(0), clazz);
	}
    
	/**
	 * @function 分页查询某个表的记录集合
	 * @author 肖荣辉
	 * @date 2023年5月30
	*/
	public static <T> List<T> findRecordListByQueryer(Class<T> clazz , DataBaseConfig dataBaseConfig , String tableName , Queryer  queryer , int pageNum , int pageSize)  throws Exception {
		
		List<LinkedHashMap<String , Object>> list = findRecordListByQueryer(dataBaseConfig, tableName, queryer, pageNum, pageSize);
		
		return CollUtil.isEmpty(list) ? new ArrayList<T>() : BeanUtil.copyToList(list , clazz);
	}
	
	/**
	 * @function 分页查询某个表的记录集合
	 * @author 肖荣辉
	 * @date 2023年5月30
	*/
	public static List<LinkedHashMap<String , Object>> findRecordListByQueryer(DataBaseConfig dataBaseConfig , String tableName , Queryer  queryer , int pageNum , int pageSize)  throws Exception {
		
		String querySql = createQuerySql(queryer);
		
		//要查询的字段
		String selectColumnTxt = CollUtil.isNotEmpty(queryer.getSelectColumnNames()) ? StrUtil.join(",", queryer.getSelectColumnNames()) : "*";
		
		  //分组字段 , 分组要放到order by前面
		if(CollUtil.isNotEmpty(queryer.getGroupByColumnNames())) {
			
			String groupByTxt = " group by ";
			
			groupByTxt +=  StrUtil.join(",", queryer.getGroupByColumnNames());
			
			querySql += groupByTxt;
		}
		
	    //having表达式
		if(CollUtil.isNotEmpty(queryer.getHavingExpressions())) {
			
			String havingTxt = " having ";
			
			havingTxt +=  StrUtil.join(",", queryer.getHavingExpressions());
			
			querySql += havingTxt;
		}
		
		String orderByTxt = "";
		
	    //每个字段按照不同的方式进行排序
		if(CollUtil.isNotEmpty(queryer.getOrderByList())) {
			
			orderByTxt = " order by ";
			
			List<String> tempList = new ArrayList<String>();
			
			for(OrderByInfo orderByInfo : queryer.getOrderByList()) {
				tempList.add(orderByInfo.getColumnName() + " " + (orderByInfo.isOrderByAsc() ? "asc" : "desc") );
			}
			
			orderByTxt +=  StrUtil.join(",", tempList);
			
		}else {
			orderByTxt = " order by SCOPE_IDENTITY() ";
		}
		
		String sql = "SELECT TOP "+pageSize+" * from (select ROW_NUMBER() over("+orderByTxt+") row_num , "+selectColumnTxt+" FROM " + tableName + querySql + ") tab  where row_num > " + PagingUtils.getOffset(pageNum, pageSize);
		
	    return executeQuerySQL(dataBaseConfig , sql);
	}
	
	
	/**
	 * @function 执行查询SQL
	 * @param sql
	 * @author 肖荣辉
	 * @date 2023年5月30
	*/
	public static <T> List<T>  executeQuerySQL(Class<T> clazz , DataBaseConfig dataBaseConfig , String sql)  throws Exception{
		
		List<LinkedHashMap<String , Object>> list = executeQuerySQL(dataBaseConfig, sql);
		
		return CollUtil.isEmpty(list) ? new ArrayList<T>() : BeanUtil.copyToList(list , clazz);
	}
	
	/**
	 * @function 执行查询SQL
	 * @param sql
	 * @author 肖荣辉
	 * @date 2023年5月30
	*/
	public static  List<LinkedHashMap<String , Object>>  executeQuerySQL(DataBaseConfig dataBaseConfig , String sql)  throws Exception{
		
		log.info("执行查询SQL : \n{}" , sql );
		
		//1. JDBC连接MYSQL的代码很标准。 
		Connection conn = getConnection(dataBaseConfig);
		
	    Statement statement = conn.createStatement();
	    
	    ResultSet rs = statement.executeQuery(sql);
	    
	    List<String> columnNameList = new ArrayList<String>();
	    
	    int size = rs.getMetaData().getColumnCount();
	    
	    ResultSetMetaData metaData = rs.getMetaData();
	    
	    Map<String , String> columnNameTypeMap = new HashMap<String, String>();
	    
	    for(int i = 1 ; i <= size ; i ++) {
	    	
	    	//获取别名，没有别名则获取真实列名
	    	String columnName = metaData.getColumnLabel(i);
	    	
	    	columnNameTypeMap.put(columnName , metaData.getColumnTypeName(i));
	    	
	    	columnNameList.add(columnName);
	    }
	    
	    List<LinkedHashMap<String , Object>> recordList = new ArrayList<LinkedHashMap<String,Object>>();
	    
        while (rs != null && rs.next()) {
        	
    	    LinkedHashMap<String , Object> record = new LinkedHashMap<String, Object>();
    	    
        	for(String columnName: columnNameList) {
        		
        		Object object = rs.getObject(columnName);
        		
        		String type = columnNameTypeMap.get(columnName);
        		
        		if("TIMESTAMP".equalsIgnoreCase(type) && object != null )
        			object = DateUtil.formatDateTime(Convert.toDate(object));
        		
        		if("DATE".equalsIgnoreCase(type) && object != null )
        			object = DateUtil.formatDate(Convert.toDate(object));
        		
        		if("DATETIME".equalsIgnoreCase(type) && object != null )
        			object = DateUtil.formatDateTime(Convert.toDate(object));
        		
        		record.put(columnName , object);
        	}
        	
        	recordList.add(record);

        }
	    
	   conn.close();
	   
	   return recordList;
	}
	
	/**
	 * @function 执行查询SQL
	 * @param sql
	 * @author 肖荣辉
	 * @date 2023年5月30
	*/
	public static  int  executeUpdateSQL(DataBaseConfig dataBaseConfig , String sql)  throws Exception{
		
		log.info("执行更新SQL : \n{}" , sql );
		
		//1. JDBC连接MYSQL的代码很标准。 
		Connection conn = getConnection(dataBaseConfig);
		
	    Statement statement = conn.createStatement();
	    
	    int count = statement.executeUpdate(sql);
	    
	   conn.close();
	   
	   return count;
	}
	
	/**
	 * @function 获取链接
	 * @author ronghui.xiao
	 * @throws ClassNotFoundException 
	 * @throws SQLException 
	 * @date 2017年6月24日
	 */
	private static Connection getConnection(DataBaseConfig config) throws ClassNotFoundException, SQLException {
	    String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
	    String url = config.getJdbcUrl();
	    String username = config.getDbUserName();
	    String password = config.getPassword();
	    Connection conn = null;
        Class.forName(driver); 
        conn = (Connection) DriverManager.getConnection(url, username, password);
	    return conn;
	}
	
}
